package com.pioneer.util;

import com.pioneer.swing.*;
import com.pioneer.dao.*;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

import java.util.Properties;
import java.io.*;
import com.pioneer.swing.ui.UIConstants;

/**
 * Title:        Pioneer Application
 * Description:
 * Copyright:    Copyright (c) 2001
 * Company:      Pioneer Metals
 * @author Gary Chen
 * @version 1.0
 */

public class JobsExporter  
implements ActionListener {
	public static final String DATABASE_DRIVER="database_driver";
	public static final String DATABASE_URL="database_url";
	public static final String DATABASE_USER="database_user";
	public static final String DATABASE_PASSWORD="database_password";	

	protected Connection connection;

    public JobsExporter(Properties properties)
	throws DataAccessException
	{
		try
		{
			Class.forName(properties.getProperty(DATABASE_DRIVER));
		}
		catch (ClassNotFoundException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		try
		{
		    connection=DriverManager.getConnection(properties.getProperty(DATABASE_URL),
		    properties.getProperty(DATABASE_USER),properties.getProperty(DATABASE_PASSWORD));
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
    }
	
	public void actionPerformed(ActionEvent e) {
	    String actionCommand=e.getActionCommand();
		boolean isSecure = false;
	    if ((PioneerApp.getCurrentRole().equals(SecurityRoles.ADMINISTRATOR)) ||
			(PioneerApp.getCurrentRole().equals(SecurityRoles.JOBQUOTE_POWERUSER) )){
			isSecure = true;
		}
		
	
	    if (actionCommand.equals(UIConstants.COMMAND_EXPORT_JOBS)) {
			try {
				String outPath = AppProperties.getProperty(AppProperties.USERDIR)+File.separator+AppProperties.getProperty(AppProperties.EXPORT_FILE_NAME);
				exportFile(new FileOutputStream(outPath),isSecure);
				String command = AppProperties.getProperty(AppProperties.EXCEL_COMMAND)+" "+
					outPath;
				Runtime.getRuntime().exec(command);	
			}
			catch (Exception ex) {
				ex.printStackTrace(System.err);
			}
		}
	}

	public void destroy()
	{
		try
		{
		    connection.close();
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
	}
	 
   /**
   * This function clears out any single quotes, replacing them with
   * sql-safe escape charactered single quotes (i.e. '-> '')
   */
	
  public static String sqlSafe(String sql){
	if (sql.startsWith("\"") && (sql.endsWith("\""))) {
		sql = sql.substring(1,sql.length()-1);
	}
	sql = sql.replaceAll("'","''");
	return sql;
  }
	
	private void exportFile(FileOutputStream fos,boolean admin)
	throws DataAccessException,IOException{		
		BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(fos));
		Statement stat=null;
		ResultSet rs=null;
		String secureFields = "";
		String secureResults = "";
		String secureHeader = "";
		if (admin) {
			secureFields = ",item.cost";
			secureHeader = ",COST";
		}
		try
		{
			stat=connection.createStatement();
			String query = "select job.quote_num,job.job_date,job.job_name,job.address,job.city,"+
				"job.state,job.zip,job.sales_rep,job.reg_sales_man,job.qty,job.type,"+
				"job.supplier_name,job.supplier_contact,job.contractor,job.contractor_contact,"+
				"job.status,job.ship_date,job.terms,job.notes,job_items.item_num,"+
				"job_items.item_desc,job_items.qty as item_qty,job_items.price"+secureFields+" "+
				"from item INNER JOIN job_items ON item.item_num=job_items.item_num "+
				"RIGHT JOIN job ON job_items.quote_num=job.quote_num order by 'job_name'";
			System.out.println(query);
			rs=stat.executeQuery(query);
			
			writer.write("QUOTE_NUM,JOB_DATE,JOB_NAME,ADDRESS,CITY,STATE,ZIP,SALES_REP,REG_SALES_MAN,"+
				"QTY,TYPE,SUPPLIER_NAME,SUPPLIER_CONTACT,CONTRACTOR,CONTRACTOR_CONTACT,STATUS,SHIP_DATE,"+
				"TERMS,NOTES,ITEM_NUM,ITEM_DESC,QTY,PRICE"+secureHeader);
			writer.newLine();
			while (rs.next())
			{
			//System.out.println("Got here");
				if (admin) {
					secureResults = ",$"+rs.getDouble("cost");
				}
				String lineOut=rs.getString("quote_num")+","+
					rs.getDate("job_date")+","+escapeComma(rs.getString("job_name"))+","+
					escapeComma(rs.getString("address"))+","+escapeComma(rs.getString("city"))+","+
					rs.getString("state")+","+rs.getString("zip")+","+
					escapeComma(rs.getString("sales_rep"))+","+escapeComma(rs.getString("reg_sales_man"))+","+
					rs.getInt("qty")+","+rs.getString("type")+","+
					escapeComma(rs.getString("supplier_name"))+","+escapeComma(rs.getString("supplier_contact"))+","+
					escapeComma(rs.getString("contractor"))+","+escapeComma(rs.getString("contractor_contact"))+","+
					rs.getString("status")+","+	rs.getDate("ship_date")+","+
					escapeComma(rs.getString("terms"))+","+ escapeComma(rs.getString("notes"))+","+rs.getString("item_num")+
					","+escapeComma(rs.getString("item_desc"))+","+rs.getInt("item_qty")+",$"+rs.getDouble("price")+
					secureResults;
				writer.write(lineOut);				
				//System.out.println(rs.getString("quote_num"));
				writer.newLine();
				//System.out.println(lineOut);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				writer.flush();
				writer.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
	}
	
	private String escapeComma(String str) {
		if (str!=null) {
			return str.replaceAll(",",";");
		}
		return str;
	}
	
	
	public static void main(String args[]) {
		try {
			if (args.length<1) {
				System.out.println("Please enter the name of the tab-delimeted file to import.");
				System.out.println("File Format: <item_num>(tab)<description>(tab)<cost>(tab)<price>");
				System.exit(1);
			}
			AppProperties.init();
			
			//pathProps.load(new FileInputStream(FileConstant.CONFIG_PATH+java.io.File.separator+FileConstant.PATHPROP));
			String userDir = AppProperties.getProperty(AppProperties.USERDIR);
			String stdErr = AppProperties.getProperty(AppProperties.ERRORFILE);
			String stdOut = AppProperties.getProperty(AppProperties.LOGFILE);
			
			
			//System.out.println("Refer to "+userDir+File.separator+stdErr+" for any possible errors and "+userDir+File.separator+stdOut+" to view items successfully imported.");
						
			System.setErr(new PrintStream(new FileOutputStream(userDir+File.separator+stdErr)));
			System.setOut(new PrintStream(new FileOutputStream(userDir+File.separator+stdOut)));
			
			//Properties props = new Properties();
			//props.load(new FileInputStream(FileConstant.CONFIG_PATH+java.io.File.separator+FileConstant.DBPROP));
			JobsExporter exporter = new JobsExporter(AppProperties.getProperties());
			exporter.exportFile(new FileOutputStream(args[0]),true);
			
		}
		catch(Exception ex) {
			ex.printStackTrace();	
		}
	}
	
}
